package helperClasses;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;

import dataTypes.Sudoku;

/**
 * 
 * @author Felix Schliephacke
 *
 * @param <T>
 */
public class DatabaseConnection<T> implements Iterable<Sudoku<T>> {

	private class DatabaseIterator implements Iterator<Sudoku<T>> {

		private Iterator<Sudoku<T>> sudokus;

		@SuppressWarnings("unchecked")
		public DatabaseIterator() {
			try {
				List<Sudoku<T>> temp = new LinkedList<Sudoku<T>>();
				ResultSet rs = select("SELECT SUDOKU FROM SUDOKU;");
				while (rs.next()) {
					temp.add((Sudoku<T>) rs.getObject(1));
				}
				this.sudokus = temp.iterator();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

		@Override
		public boolean hasNext() {
			return this.sudokus.hasNext();
		}

		@Override
		public Sudoku<T> next() {
			return this.sudokus.next();
		}

		@Override
		public void remove() {
			this.sudokus.remove();
		}

	}

	private static Connection connection;
	private static final String DB = "solutions";

	private static DatabaseConnection<?> instance;

	private static void connect() throws SQLException {
		if (connection == null || connection.isClosed()) {
			connection = DriverManager.getConnection("jdbc:hsqldb:file:" + DB
					+ "/db;shutdown=false");
			connection.setAutoCommit(true);
		}
	}

	private static Connection getConnection() throws SQLException {
		connect();
		if (connection == null || connection.isClosed()) {
			throw new SQLException(
					"Connection to the database could not be established.");
		}
		return connection;
	}

	@SuppressWarnings("unchecked")
	public static DatabaseConnection<Integer> getInstance() {
		if (instance == null) {
			try {
				instance = new DatabaseConnection<Integer>();
			} catch (ClassNotFoundException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return (DatabaseConnection<Integer>) instance;
	}

	private DatabaseConnection() throws ClassNotFoundException, SQLException {

		// load jdbcDriver
		Class.forName("org.hsqldb.jdbcDriver");

		this.createDatabase();
	}

	private void close() throws SQLException {
		if (connection != null && !connection.isClosed()) {
			connection.close();
		}
	}

	private synchronized void createDatabase() throws SQLException {

		// Create tables

		this.createTable("SUDOKU", "ID INTEGER IDENTITY",
				"SUDOKU OTHER NOT NULL");
		this.close();
	}

	private synchronized void createTable(String tableName, String... columns)
			throws SQLException {

		if (tableName == null || tableName.length() == 0) {
			throw new IllegalArgumentException("Table name must be set.");
		}
		if (columns.length == 0) {
			throw new IllegalArgumentException(
					"At least one column must be defined.");
		}

		StringBuffer temp = new StringBuffer(columns[0]);
		for (int i = 1; i < columns.length; i++) {
			temp.append("," + columns[i]);
		}

		Statement stmt = getConnection().createStatement();

		try {
			stmt.execute("CREATE TABLE " + tableName + "(" + temp + ");");
		} catch (SQLException e) {
			if (new String("object name already exists: " + tableName).equals(e
					.getCause().getMessage())) {
			}
		}
		stmt.close();
	}

	public synchronized void insertSudoku(Sudoku<T> sudoku) {
		try {
			PreparedStatement st = this
					.prepareStatement("INSERT INTO SUDOKU(SUDOKU) VALUES(?);");
			st.setObject(1, sudoku);
			st.executeUpdate();
			st.close();
			this.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	@Override
	public Iterator<Sudoku<T>> iterator() {
		return new DatabaseIterator();
	}

	private synchronized PreparedStatement prepareStatement(String sql)
			throws SQLException {
		return getConnection().prepareStatement(sql);
	}

	private synchronized PreparedStatement prepareStatementAndReturnGeneratedKeys(
			String sql) throws SQLException {
		return getConnection().prepareStatement(sql,
				Statement.RETURN_GENERATED_KEYS);
	}

	synchronized ResultSet select(String statement) throws SQLException {
		Statement stmt = getConnection().createStatement();
		ResultSet rs = stmt.executeQuery(statement);
		stmt.close();
		this.close();
		return rs;
	}

	synchronized int update(String expression) throws SQLException {
		Statement stmt = getConnection().createStatement();
		int temp = stmt.executeUpdate(expression);
		stmt.close();
		this.close();
		return temp;
	}
}
